package com.bootdo.common.excelread.service;


import com.bootdo.common.excelread.service.model.ColumnFieldMapper;
import com.bootdo.common.exception.ParmException;
import com.bootdo.common.utils.ExcelUtil;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@Service
public class ExcelReadImp implements ExcelRead {

    private Logger logger = LoggerFactory.getLogger(getClass());

    public int totalRows; //sheet中总行数
    public static int totalCells; //每一行总单元格数
    /**
     * read the Excel .xlsx,.xls
     * @param file 上传的文件
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readExcel(MultipartFile file) throws IOException {
        if( file==null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim()))
            return null;
        String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
        if(!ExcelUtil.EMPTY.equals(postfix)){
            if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix))
                return readXls(file);
            else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix))
                return readXlsx(file);
            else
                return null;
        }
        return null;
    }
    /**
     * read the Excel 2010 .xlsx
     * @param file
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readXlsx(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<>();
        InputStream input = null;// IO流读取文件
        XSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            wb = new XSSFWorkbook(input);// 创建文档
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){//读取sheet(页)
                XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
                if(xssfSheet == null)
                    continue;
                totalRows = xssfSheet.getLastRowNum();
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){//读取Row,从第二行开始
                    XSSFRow xssfRow = xssfSheet.getRow(rowNum);
                    if(xssfRow!=null){
                        rowList = new ArrayList<>();
                        totalCells = xssfRow.getLastCellNum();
                        for(int c=0;c<=totalCells+1;c++){ //读取列，从第一列开始
                            XSSFCell cell = xssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                //continue;
                            }else//--add--
                            rowList.add(ExcelUtil.getXValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            if(logger.isErrorEnabled())
                logger.error(e.getMessage(),e);
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                if(logger.isErrorEnabled())
                    logger.error(e.getMessage(),e);
            }
        }
        return null;

    }
    /**
     * read the Excel 2003-2007 .xls
     * @param file
     * @return
     * @throws IOException
     */
    public List<ArrayList<String>> readXls(MultipartFile file){
        List<ArrayList<String>> list = new ArrayList<>();
        InputStream input = null;// IO流读取文件
        HSSFWorkbook wb = null;
        ArrayList<String> rowList = null;
        try {
            input = file.getInputStream();
            wb = new HSSFWorkbook(input);// 创建文档
            for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){//读取sheet(页)
                HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
                if(hssfSheet == null)
                    continue;
                totalRows = hssfSheet.getLastRowNum();
                for(int rowNum = 1;rowNum <= totalRows;rowNum++){//读取Row,从第二行开始
                    HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    if(hssfRow!=null){
                        rowList = new ArrayList<>();
                        totalCells = hssfRow.getLastCellNum();
                        for(short c=0;c<=totalCells+1;c++){//读取列，从第一列开始
                            HSSFCell cell = hssfRow.getCell(c);
                            if(cell==null){
                                rowList.add(ExcelUtil.EMPTY);
                                //continue;
                            }else //--add--
                            rowList.add(ExcelUtil.getHValue(cell).trim());
                        }
                        list.add(rowList);
                    }
                }
            }
            return list;
        } catch (IOException e) {
            if(logger.isErrorEnabled())
                logger.error(e.getMessage(),e);
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                if(logger.isErrorEnabled())
                    logger.error(e.getMessage(),e);
            }
        }
        return null;
    }

    public List<ArrayList<String>> readExcel(MultipartFile file, ColumnFieldMapper columnFieldMapper, Boolean isReadAllSheet) throws Exception {
        if( file==null || ExcelUtil.EMPTY.equals(file.getOriginalFilename().trim()))
            return null;
        String postfix = ExcelUtil.getPostfix(file.getOriginalFilename());
        if(!ExcelUtil.EMPTY.equals(postfix)){
            if(ExcelUtil.OFFICE_EXCEL_2003_POSTFIX.equals(postfix))
                return readXls(file,columnFieldMapper,isReadAllSheet);
            else if(ExcelUtil.OFFICE_EXCEL_2010_POSTFIX.equals(postfix))
                return readXlsx(file,columnFieldMapper,isReadAllSheet);
            else
                return null;
        }
        return null;
    }

    public List<ArrayList<String>> readXlsx(MultipartFile file, ColumnFieldMapper columnFieldMapper,Boolean isReadAllSheet){
        List<ArrayList<String>> list = new ArrayList<>();
        InputStream input = null;// IO流读取文件
        XSSFWorkbook wb = null;
        try {
            input = file.getInputStream();
            wb = new XSSFWorkbook(input);// 创建文档
            if(isReadAllSheet==true){
                for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){//读取sheet(页)
                    readSheet(wb,numSheet,columnFieldMapper,list);
                }
            }else{
                readSheet(wb,0,columnFieldMapper,list);
            }
            return list;
        } catch (IOException e) {
            if(logger.isErrorEnabled())
                logger.error(e.getMessage(),e);
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                if(logger.isErrorEnabled())
                    logger.error(e.getMessage(),e);
            }
        }
        return null;
    }

    private void readSheet(XSSFWorkbook wb,int numSheet,ColumnFieldMapper columnFieldMapper,List<ArrayList<String>> list){
        ArrayList<String> rowList = null;
        XSSFSheet xssfSheet = wb.getSheetAt(numSheet);
        if(xssfSheet == null)
            return;
        totalRows = xssfSheet.getLastRowNum();
        for(int rowNum = 0;rowNum <= totalRows;rowNum++){//读取Row,第一行为表头
            if(rowNum==0){
                readFirstRow(xssfSheet,columnFieldMapper);
                continue;
            }
            XSSFRow xssfRow = xssfSheet.getRow(rowNum);
            if(xssfRow!=null){
                rowList = new ArrayList<>();
                totalCells = xssfRow.getLastCellNum();
                for(short c=0;c<=totalCells+1;c++){//读取列，从第一列开始
                    XSSFCell cell = xssfRow.getCell(c);
                    if(cell==null)
                        rowList.add(ExcelUtil.EMPTY);
                    else
                        rowList.add(ExcelUtil.getXValue(cell).trim());
                }
                list.add(rowList);
            }
        }
    }

    private void readFirstRow(XSSFSheet xssfSheet,ColumnFieldMapper columnFieldMapper){
        Map map = columnFieldMapper.getMap();//map不能为空
        List columnNameList = columnFieldMapper.getColumnNameList();//list 不能为空
        ArrayList<String> rowList = new ArrayList<>();//保存excel的列名
        XSSFRow xssfRow = xssfSheet.getRow(0);
        if(xssfRow!=null){
            totalCells = xssfRow.getLastCellNum();
            for(short c=0;c<=totalCells+1;c++){//读取列，从第一列开始
                XSSFCell cell = xssfRow.getCell(c);
                if(cell!=null)
                    rowList.add(ExcelUtil.getXValue(cell).trim());
            }
        }
        //列名不为空，判断列名称是否存在ColumnFieldMapper中
        if(rowList.containsAll(columnNameList)&&columnNameList.containsAll(rowList)){
            return;
        }  else {
            columnNameList.removeAll(rowList);
            throw new ParmException("excel中未发现列："+columnNameList.toString());
        }
    }

    public List<ArrayList<String>> readXls(MultipartFile file, ColumnFieldMapper columnFieldMapper,Boolean isReadAllSheet){
        List<ArrayList<String>> list = new ArrayList<>();
        InputStream input = null;// IO流读取文件
        HSSFWorkbook wb = null;
        try {
            input = file.getInputStream();
            wb = new HSSFWorkbook(input);// 创建文档
            if(isReadAllSheet==true){
                for(int numSheet=0;numSheet<wb.getNumberOfSheets();numSheet++){//读取sheet(页)
                    readSheet(wb,numSheet,columnFieldMapper,list);
                }
            }else{
                readSheet(wb,0,columnFieldMapper,list);
            }
            return list;
        } catch (IOException e) {
            if(logger.isErrorEnabled())
                logger.error(e.getMessage(),e);
        } finally{
            try {
                input.close();
            } catch (IOException e) {
                if(logger.isErrorEnabled())
                    logger.error(e.getMessage(),e);
            }
        }
        return null;
    }

    private void readSheet(HSSFWorkbook wb,int numSheet,ColumnFieldMapper columnFieldMapper,List<ArrayList<String>> list){
        ArrayList<String> rowList = null;
        HSSFSheet hssfSheet = wb.getSheetAt(numSheet);
        if(hssfSheet == null)
            return;
        totalRows = hssfSheet.getLastRowNum();
        for(int rowNum = 0;rowNum <= totalRows;rowNum++){//读取Row,第一行为表头
            if(rowNum==0){
                readFirstRow(hssfSheet,columnFieldMapper);
                continue;
            }
            HSSFRow hssfRow = hssfSheet.getRow(rowNum);
            if(hssfRow!=null){
                rowList = new ArrayList<>();
                totalCells = hssfRow.getLastCellNum();
                for(short c=0;c<=totalCells+1;c++){//读取列，从第一列开始
                    HSSFCell cell = hssfRow.getCell(c);
                    if(cell==null)
                        rowList.add(ExcelUtil.EMPTY);
                    else
                        rowList.add(ExcelUtil.getHValue(cell).trim());
                }
                list.add(rowList);
            }
        }
    }

    private void readFirstRow(HSSFSheet hssfSheet,ColumnFieldMapper columnFieldMapper){
        Map map = columnFieldMapper.getMap();//map不能为空
        List columnNameList = columnFieldMapper.getColumnNameList();//list 不能为空
        ArrayList<String> rowList = new ArrayList<>();//保存excel的列名
        HSSFRow hssfRow = hssfSheet.getRow(0);
        if(hssfRow!=null){
            totalCells = hssfRow.getLastCellNum();
            for(short c=0;c<=totalCells+1;c++){//读取列，从第一列开始
                HSSFCell cell = hssfRow.getCell(c);
                if(cell!=null)
                    rowList.add(ExcelUtil.getHValue(cell).trim());
            }
        }
        //列名不为空，判断列名称是否存在ColumnFieldMapper中
        if(rowList.containsAll(columnNameList)&&columnNameList.containsAll(rowList)){
            return;
        }  else {
            columnNameList.removeAll(rowList);
            if(columnNameList.size()>0)
             throw new ParmException("excel中未发现列："+columnNameList.toString());
        }
    }

    private boolean isMapNameExist(String name,List list){
        if(list!=null&&list.contains(name))
            return true;
        else
            return false;
    }


}
